package com.zr.gdms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import com.zr.gdms.dao.StudentDaoSCM;
import com.zr.gdms.model.Course;
import com.zr.gdms.model.Student;
import com.zr.gdms.util.JDBCUtil;

/**
 * @author  shichongming
 * @E-mail: 
 * @date 创建时间：2017年5月25日 上午10:42:57 
 * @version 1.0 
 * @parameter  
 * @since  
 * @return  
 */

public class StudentDaoImplSCM implements StudentDaoSCM{

	
	//查询学生信息通过学生用户的uid----石铳铭
	@Override
	public Student findStudentByUid(int uid) {
Student stu=new Student();
StringBuffer sql=new StringBuffer("");
sql.append(" select student.*");;
sql.append(" from student");
sql.append(" INNER JOIN `user`");
sql.append(" on student.uid=?");

Connection con=JDBCUtil.getConnection();
try {
	PreparedStatement pst=con.prepareStatement(sql.toString());
	pst.setInt(1,uid);
	ResultSet rs=pst.executeQuery();
	while(rs.next()){
		stu.setSid(rs.getInt("sid"));
		stu.setSname(rs.getString("sname"));
		stu.setSsex(rs.getString("ssex"));
		stu.setSage(rs.getString("sage"));
		stu.setStel(rs.getString("stel"));
		stu.setSdepart(rs.getString("sdepart"));
		stu.setSstate(rs.getString("sstate"));
	}	
} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}
		return stu;
	}

	
	//修改学生信息----石铳铭
	@Override
	public boolean updateStudentInfo(int sid, String sname, String sage,
			String ssex, String sdepart, String stel) {
		              boolean flag=false;
		StringBuffer sql=new StringBuffer("");
		sql.append(" UPDATE student SET sname=?,sage=?,ssex=?,sdepart=?,stel=?");
		sql.append(" WHERE sid =?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());		
			pst.setString(1,sname);
			pst.setString(2,sage);
			pst.setString(3,ssex);
			pst.setString(4,sdepart);
			pst.setString(5,stel);
			pst.setInt(6,sid);
			int rs=pst.executeUpdate();
			if(rs>0){
				flag=true;
			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}


	
	
	
	
	
	//选择课程并分页----石铳铭
	//1,求出分页的总数count----石铳铭	  
	@Override
	public int findCount(String cstate) {
		int s=0;
		StringBuffer sql=new StringBuffer("");
		sql.append(" SELECT count(*)as total ");
		sql.append(" from course");
		sql.append(" where course.cstate=?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1,cstate);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				s=rs.getInt("total");
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return s;
	}
	
	
	//2,分页的要求----石铳铭
	@Override
	public JSONArray findCourseFenYe( String cstate,int page, int rows) {
		JSONArray j=new JSONArray();
		StringBuffer sql=new StringBuffer("");
		sql.append(" select course.cid,course.cname,course.cstate,course.tid,teacher.tname,teacher.tdepart,teacher.tprofe,teacher.ttel");
		sql.append(" from course");
		sql.append(" INNER JOIN teacher");
		sql.append(" on course.tid=teacher.tid");
		sql.append(" where course.cstate=? limit ?,?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1,cstate);
			pst.setInt(2, page);
			pst.setInt(3,rows);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				JSONObject js=new JSONObject();
				js.put("cid", rs.getInt("cid"));
				js.put("cname", rs.getString("cname"));
				js.put("cstate", rs.getString("cstate"));
				js.put("tid", rs.getInt("tid"));
				js.put("tname", rs.getString("tname"));
				js.put("tdepart", rs.getString("tdepart"));
				js.put("tprofe", rs.getString("tprofe"));
				js.put("ttel", rs.getString("ttel"));	
				j.add(js);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return j;
	}


	
	//选择课题----石铳铭
	//1，选择课题后通过cid将课题的状态更改为true----石铳铭
	@Override
	public boolean updatecourseCstateBycid(int cid ) {
		boolean flag=false;
		StringBuffer sql=new StringBuffer("");
		sql.append(" update course set course.cstate='true'");
		sql.append(" where course.cid=?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1,cid);
			int  rs=pst.executeUpdate();
			if(rs>0){
				flag=true;				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return flag;
	}

	//2.选择课题后对score成绩记录进行新增sid和cid数据----石铳铭
	@Override
	public boolean insertscoreBySidCid(int sid, int cid) {	
		boolean flag=false;
		StringBuffer sql=new StringBuffer("");
		sql.append(" insert into score(sid,cid) values(?,?)");
		
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1,sid);
			pst.setInt(2,cid);
			int  rs=pst.executeUpdate();
			if(rs>0){
				flag=true;				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return flag;
	}
	//3.查询学生是否已经选择课题----石铳铭
	@Override
	public int selectCourseExistBySid(int sid) {
		int a=0;
		StringBuffer sql=new StringBuffer("");
		sql.append(" select score.cid");
		sql.append(" from score ");
		sql.append(" where score.sid=?");
	Connection con = JDBCUtil.getConnection();
	try {
		PreparedStatement pst=con.prepareStatement(sql.toString());
		pst.setInt(1,sid);
		ResultSet rs=pst.executeQuery();
		while(rs.next()){
			a=rs.getInt("cid");
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
		return a;
	}


	
	
	//查询学生自己已选择的课题----石铳铭
	@Override
	public JSONArray findCourseBySid(int sid) {
		
		JSONArray j=new JSONArray();
		StringBuffer sql=new StringBuffer("");
		sql.append(" select course.cid,course.cname,course.cstate,course.tid,teacher.tname,teacher.tdepart,teacher.tprofe,teacher.ttel");
		sql.append(" from course");
		sql.append(" INNER JOIN teacher");
		sql.append(" on course.tid=teacher.tid");
		sql.append(" INNER JOIN score");
		sql.append(" on score.cid=course.cid");
		sql.append(" where score.sid=?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			
			pst.setInt(1,sid);
			
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				JSONObject js=new JSONObject();
				js.put("cid", rs.getInt("cid"));
				js.put("cname", rs.getString("cname"));
				js.put("cstate", rs.getString("cstate"));
				js.put("tid", rs.getInt("tid"));
				js.put("tname", rs.getString("tname"));
				js.put("tdepart", rs.getString("tdepart"));
				js.put("tprofe", rs.getString("tprofe"));
				js.put("ttel", rs.getString("ttel"));	
				j.add(js);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return j;
	}


	
	
	//修改学生已选择的课题----石铳铭
	//1.修改旧的课题的状态----石铳铭
	@Override
	public boolean updateOldCstate(int cid) {
		  boolean flag=false;
		StringBuffer sql=new StringBuffer("");
		  sql.append(" update course set course.cstate='false'");
		  sql.append(" where course.cid=?");
		 Connection con=JDBCUtil.getConnection();
		 PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());		
			 pst.setInt(1,cid);
			 int rs=pst.executeUpdate();
			 if(rs>0){
				 flag=true;
			 }			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	//2.在score表格中根据学生的sid，查询对应的cid----石铳铭
	@Override
	public int findCidBySid(int sid) {
	  int a=0;	  
	  StringBuffer sql=new StringBuffer("");
	  sql.append(" select score.cid");
	  sql.append(" from score");
	  sql.append(" where score.sid=?");
Connection con = JDBCUtil.getConnection();
try {
	PreparedStatement pst=con.prepareStatement(sql.toString());
	pst.setInt(1,sid);
	ResultSet rs=pst.executeQuery();
			if(rs.next()){
				a=rs.getInt("cid");
		}
} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}
		return a;
	}

	//3.删除旧的数据库----石铳铭
	@Override
	public boolean deleteScoreBysid(int sid) {
		  boolean flag=false;
			StringBuffer sql=new StringBuffer("");
			  sql.append(" delete from score ");
			  sql.append(" where score.sid=?");
			 Connection con=JDBCUtil.getConnection();
			 PreparedStatement pst;
			try {
				pst = con.prepareStatement(sql.toString());		
				 pst.setInt(1,sid);
				 int rs=pst.executeUpdate();
				 if(rs>0){
					 flag=true;
				 }			 
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return flag;
	}


	
	//查询学生自己的信息----石铳铭
	@Override
	public JSONArray searchStudentMyselfScore(int sid) {
		JSONArray js=new JSONArray();
		StringBuffer sql=new StringBuffer("");
		sql.append(" select score.sid,score.cid,course.cname,score.score");
		sql.append(" from score");
		sql.append(" inner join course");
		sql.append(" on score.cid=course.cid");
		sql.append(" where score.sid=?");
	Connection con=JDBCUtil.getConnection();
	try {
		PreparedStatement pst=con.prepareStatement(sql.toString());
		      pst.setInt(1,sid);
		ResultSet rs=pst.executeQuery();
		while(rs.next()){
			JSONObject j=new JSONObject();
			j.put("sid", rs.getInt("sid"));
			j.put("cid", rs.getInt("cid"));
			j.put("score", rs.getString("score"));
			j.put("cname", rs.getString("cname"));
			js.add(j);			
		}		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
		return js;
	}
				
	

	
	
	
	

}



